﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using System.IO;
using DevComponents.DotNetBar.SuperGrid;

namespace KP_CommonLibrary.KP_Function
{
    public class ExportExcel
    {
        public static void ExportToTemplate(DataTable tblData, int templateID)
        {

        }

        public static void ExportToExcel(DataTable tblData, string fileName, string sheetName)
        {
            if (string.IsNullOrEmpty(sheetName))
                sheetName = "Sheet1";

            HSSFWorkbook book = new HSSFWorkbook();
            ISheet sheet = book.CreateSheet(sheetName);
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            cell.SetCellValue(tblData.TableName);

            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, tblData.Columns.Count));

            row = sheet.CreateRow(2);
            int index = 0;
            ICellStyle style = book.CreateCellStyle();
            style.Alignment = HorizontalAlignment.JUSTIFY;
            style.BorderBottom = CellBorderType.THIN;
            style.BorderLeft = CellBorderType.THIN;
            style.BorderRight = CellBorderType.THIN;
            style.BorderTop = CellBorderType.THIN;
            style.FillBackgroundColor = HSSFColor.DARK_YELLOW.index;
            style.LeftBorderColor = 64;
            style.RightBorderColor = 64;
            style.TopBorderColor = 64;
            style.BottomBorderColor = 64;
            style.FillPattern = FillPatternType.SOLID_FOREGROUND;
            style.FillBackgroundColor = 64;
            style.FillForegroundColor = 22;
            style.ShrinkToFit = true;
            IFont font = book.CreateFont();
            font.FontName = "Arial";
            font.FontHeight = 10;
            font.FontHeightInPoints = 10;
            style.SetFont(font);


            foreach (DataColumn col in tblData.Columns)
            {
                cell = row.CreateCell(index++);
                cell.SetCellValue(string.IsNullOrEmpty(col.Caption) ? col.ColumnName : col.Caption);
                cell.CellStyle = style;
            }

            ICellStyle content = book.CreateCellStyle();
            content.CloneStyleFrom(style);
            content.FillBackgroundColor = 0;
            content.FillPattern = FillPatternType.NO_FILL;
            
            int rowIndex = 3;
            foreach (DataRow node in tblData.Rows)
            {
                row = sheet.CreateRow(rowIndex++);
                index = 0;
                foreach (DataColumn col in tblData.Columns)
                {
                    cell = row.CreateCell(index);
                    index++;
                    cell.CellStyle = content;
                    if (node.IsNull(col))
                        continue;
                    if (col.DataType == typeof(DateTime))
                    {
                        cell.SetCellValue((DateTime)node[col]);
                    }
                    if (col.DataType == typeof(bool))
                    {
                        cell.SetCellValue((bool)node[col]);
                    }
                    else
                        cell.SetCellValue(node.IsNull(col) ? "" : node[col].ToString());

                }
            }
            using (FileStream fs = new FileStream(fileName, FileMode.Create))
            {
                book.Write(fs);
                fs.Flush();
                fs.Close();
            }


        }

        public static void ExportToExcel(DevComponents.DotNetBar.SuperGrid.SuperGridControl grid, string fileName, string sheetName)
        {
            if (string.IsNullOrEmpty(sheetName))
                sheetName = "Sheet1";

            HSSFWorkbook book = new HSSFWorkbook();
            ISheet sheet = book.CreateSheet(sheetName);
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            cell.SetCellValue(grid.PrimaryGrid.Caption.Text);

            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, grid.PrimaryGrid.Columns.Count));

            row = sheet.CreateRow(2);
            int index = 0;
            ICellStyle style = book.CreateCellStyle();
            style.Alignment = HorizontalAlignment.JUSTIFY;
            style.BorderBottom = CellBorderType.THIN;
            style.BorderLeft = CellBorderType.THIN;
            style.BorderRight = CellBorderType.THIN;
            style.BorderTop = CellBorderType.THIN;
            style.FillBackgroundColor = HSSFColor.DARK_YELLOW.index;
            style.LeftBorderColor = 64;
            style.RightBorderColor = 64;
            style.TopBorderColor = 64;
            style.BottomBorderColor = 64;
            style.FillPattern = FillPatternType.SOLID_FOREGROUND;
            style.FillBackgroundColor = 64;
            style.ShrinkToFit = true;
            style.FillForegroundColor = 22;
            IFont font = book.CreateFont();
            font.FontName = "Arial";
            font.FontHeight = 10;
            font.FontHeightInPoints = 10;
            style.SetFont(font);


            foreach (GridColumn col in grid.PrimaryGrid.Columns)
            {
                cell = row.CreateCell(index++);
                cell.SetCellValue(col.HeaderText);
                cell.CellStyle = style;
            }

            ICellStyle content = book.CreateCellStyle();
            content.CloneStyleFrom(style);
            content.FillBackgroundColor = 0;
            content.FillPattern = FillPatternType.NO_FILL;
            
            int rowIndex = 3;
            foreach (GridElement node in grid.PrimaryGrid.Rows)
            {
                row = sheet.CreateRow(rowIndex++);
                GridRow gRow = node as GridRow;
                index = 0;
                foreach (GridColumn col in grid.PrimaryGrid.Columns)
                {

                    cell = row.CreateCell(index++);
                   // index++;
                    cell.CellStyle = content;
                    
                    if (gRow[col].IsValueNull)
                        continue;
                    object data = gRow[col].Value;
                    if (data.GetType() == typeof(DateTime))
                    {
                        cell.SetCellValue((DateTime)data);
                    }
                    if (data.GetType() == typeof(bool))
                    {
                        cell.SetCellValue((bool)data);
                    }
                    else
                        cell.SetCellValue(data.ToString());

                }
            }
            using (FileStream fs = new FileStream(fileName, FileMode.Create))
            {
                book.Write(fs);
                fs.Flush();
                fs.Close();
            }


        }


    }

    public class ExcelTemplate
    {
        public string TemplateFileName { get; set; }
        public string TemplateSheetName { get; set; }
        public string ExportToSheet { get; set; }
        public int BeginDataRow { get; set; }
        public int CaptionRow { get; set; }

        public Dictionary<string, int> Map { get; set; }

        public Dictionary<int, string> ReverseMap
        {
            get
            {

                Dictionary<int, string> temp = new Dictionary<int, string>();
                foreach (KeyValuePair<string, int> node in Map)
                {
                    temp.Add(node.Value, node.Key);
                }
                return temp;
            }
        }


    }
}
